package com.kinglone.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.Region;

public class ExportXls<T> {
		 
	   /**
	    * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	    *
	    * @param title
	    *            表格标题名
	    * @param headers
	    *            表格属性列名数组
	    * @param dataset
	    *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	    *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	    * @param out
	    *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	    * @param pattern
	    *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	    */
	   @SuppressWarnings("unchecked")
	   public HSSFWorkbook exportExcel(String title,String subTitle ,String[] headers, Collection<T> dataset,Double consumeMoney, Double totalMoney) {
			 try {
		      // 声明一个工作薄
		      HSSFWorkbook workbook = new HSSFWorkbook();
		      // 生成一个表格
		      HSSFSheet sheet = workbook.createSheet(title);
		      // 设置表格默认列宽度为15个字节
//		      sheet.autoSizeColumn((short)0);
//		      sheet.setDefaultColumnWidth((short) 20);
		      sheet.addMergedRegion(new Region(0,(short)0,0,(short)(headers.length-1))); //合并第一行
		      sheet.addMergedRegion(new Region(1,(short)0,1,(short)(headers.length-1))); //合并第二行
		      // 生成一个样式
		      HSSFCellStyle style = workbook.createCellStyle();
		      // 设置这些样式
		      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		      style.setFillBackgroundColor(HSSFColor.AQUA.index);
		      style.setFillForegroundColor(HSSFColor.BLACK.index);
		      // 生成一个字体
		      HSSFFont font = workbook.createFont();
		      font.setColor(HSSFColor.BLACK.index);
		      font.setFontHeightInPoints((short) 12);
		      font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		      // 把字体应用到当前的样式
		      style.setFont(font);
		      	//创建标题
			     HSSFRow row = sheet.createRow(0);
			     HSSFCell cell0 = row.createCell(0);
		         cell0.setCellStyle(style);
		         HSSFRichTextString text0 = new HSSFRichTextString(title);
		         cell0.setCellValue(text0);
		         
		       //创建查询条件行
			     row = sheet.createRow(1);
			     cell0 = row.createCell(0);
		         cell0.setCellStyle(style);
		         text0 = new HSSFRichTextString(subTitle);
		         cell0.setCellValue(text0);
		         
		      //产生表格标题行
		      row = sheet.createRow(2);
		      for (short i = 0; i < headers.length; i++) {
		         HSSFCell cell = row.createCell(i);
		         cell.setCellStyle(style);
		         HSSFRichTextString text = new HSSFRichTextString(headers[i]);
		         cell.setCellValue(text);
		      }
		 
		      //遍历集合数据，产生数据行
		      Iterator<T> it = dataset.iterator();
		      int index = 2;
		      while (it.hasNext()) {
		         index++;
		         row = sheet.createRow(index);
		         T t = (T) it.next();
		         //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
		         Field[] fields = t.getClass().getDeclaredFields();
		         for (short i = 0; i < fields.length; i++) {
		           
		            Field field = fields[i];
		            String fieldName = field.getName();
		            String getMethodName = "get"
		                   + fieldName.substring(0, 1).toUpperCase()
		                   + fieldName.substring(1);
		            Class tCls = t.getClass();
	                Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
	                Object value = getMethod.invoke(t, new Object[] {});
	                //判断值的类型后进行强制类型转换
	                String textValue;
	                if(value!=null){
	                	textValue = value.toString()+"";
	                	int headerLength = headers[i].getBytes().length*2*256;
	                	int cellLength = textValue.getBytes().length*2*256;
	                	if (headerLength > cellLength) {
		                	sheet.setColumnWidth(i, headerLength);
						}else{
							sheet.setColumnWidth(i, cellLength);
						}
	                }else{
	                	textValue = "";
	                }
	                HSSFCell cell = row.createCell(i);
		            cell.setCellStyle(style);
	                HSSFRichTextString richString = new HSSFRichTextString(textValue);
	                cell.setCellValue(richString);
		         }
		      }
		   
//		      short colspan = (short) (headers.length / 2 - 1);
//		      row = sheet.createRow((++index));
//		      for(int i = 0 ; i < headers.length ; i ++ ) {
//			      HSSFCell cell = row.createCell(i);
//		          cell.setCellStyle(style);
//		          if(i == 0 && consumeMoney!=0) {
//		        	  HSSFRichTextString richString = new HSSFRichTextString("应收总金额：");
//		        	  HSSFFont ftRed = workbook.createFont();  
//		              ftRed.setStrikeout(true);  
//		              ftRed.setColor(HSSFColor.RED.index);
//		        	  richString.applyFont(font);
//		        	  cell.setCellValue(richString);
//		          }else if(i == (colspan + 1)&& consumeMoney!=0) {
//		        	  HSSFRichTextString richString = new HSSFRichTextString(consumeMoney + "元");
//		              cell.setCellValue(richString);
//		          }
//		      }
//		      sheet.addMergedRegion(new Region(index, (short)0, index, colspan));
//	          sheet.addMergedRegion(new Region(index, (short)(colspan+1), index, (short)(headers.length-1)));
//	          row = sheet.createRow((++index));
//		      for(int i = 0 ; i < headers.length ; i ++ ) {
//			      HSSFCell cell = row.createCell(i);
//		          cell.setCellStyle(style);
//		          if(i == 0 && totalMoney!=0) {
//		        	  HSSFRichTextString richString = new HSSFRichTextString("实收总金额：");
//		        	  HSSFFont ftRed = workbook.createFont();  
//		              ftRed.setStrikeout(true);  
//		              ftRed.setColor(HSSFColor.RED.index);
//		        	  richString.applyFont(font);
//		        	  cell.setCellValue(richString);
//		          }else if(i == (colspan + 1)&& totalMoney!=0) {
//		        	  HSSFRichTextString richString = new HSSFRichTextString(totalMoney + "元");
//		              cell.setCellValue(richString);
//		          }
//		      }
//		      sheet.addMergedRegion(new Region(index, (short)0, index, colspan));
//	          sheet.addMergedRegion(new Region(index, (short)(colspan+1), index, (short)(headers.length-1)));
		      return workbook;
			 }catch(Exception e) {
				e.printStackTrace();
			 }
		  return null;
	   }
	   
	   
	   public HSSFWorkbook exportExcel(String title, String[] headers, Collection<T> dataset) {
			   try {
		      // 声明一个工作薄
		      HSSFWorkbook workbook = new HSSFWorkbook();
		      // 生成一个表格
		      HSSFSheet sheet = workbook.createSheet(title);
		      // 设置表格默认列宽度为15个字节
		      sheet.setDefaultColumnWidth((short) 20);
		      // 生成一个样式
		      HSSFCellStyle style = workbook.createCellStyle();
		      // 设置这些样式
		      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		      // 生成一个字体
		      HSSFFont font = workbook.createFont();
		      font.setColor(HSSFColor.BLACK.index);
		      font.setFontHeightInPoints((short) 12);
		      font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		      // 把字体应用到当前的样式
		      style.setFont(font);
		      
		      //产生表格标题行
		      HSSFRow row = sheet.createRow(0);
		      for (short i = 0; i < headers.length; i++) {
		         HSSFCell cell = row.createCell(i);
		         cell.setCellStyle(style);
		         HSSFRichTextString text = new HSSFRichTextString(headers[i]);
		         cell.setCellValue(text);
		      }
		 
		      //遍历集合数据，产生数据行
		      Iterator<T> it = dataset.iterator();
		      int index = 0;
		      while (it.hasNext()) {
		         index++;
		         row = sheet.createRow(index);
		         T t = (T) it.next();
		         //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
		         Field[] fields = t.getClass().getDeclaredFields();
		         for (short i = 0; i < fields.length; i++) {
		            HSSFCell cell = row.createCell(i);
		            cell.setCellStyle(style);
		            Field field = fields[i];
		            String fieldName = field.getName();
		            String getMethodName = "get"
		                   + fieldName.substring(0, 1).toUpperCase()
		                   + fieldName.substring(1);
		            Class tCls = t.getClass();
	                Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
	                Object value = getMethod.invoke(t, new Object[] {});
	                //判断值的类型后进行强制类型转换
	                //20150724  yhd
	                String textValue;
	                if(value!=null){
	                	textValue = value.toString()+"";
	                }else{
	                	textValue = "";
	                }
	                HSSFRichTextString richString = new HSSFRichTextString(textValue);
	                cell.setCellValue(richString);
		         }
		      }
		   
		      short colspan = (short) (headers.length / 2 - 1);
		      row = sheet.createRow((++index));
		      for(int i = 0 ; i < headers.length ; i ++ ) {
			      HSSFCell cell = row.createCell(i);
		          cell.setCellStyle(style);
		      }
		      sheet.addMergedRegion(new Region(index, (short)0, index, colspan));
	          sheet.addMergedRegion(new Region(index, (short)(colspan+1), index, (short)(headers.length-1)));
	          
		      return workbook;
			   }catch(Exception e) {
				   e.printStackTrace();
			   }
			   return null;
	    }
	   
	   public HSSFWorkbook exportExcel(String title,String subTitle,String[] headers,Collection<T>dataset){
		   try {
			HSSFWorkbook workbook =new HSSFWorkbook();
			HSSFSheet sheet =workbook.createSheet(title);
			 sheet.setDefaultColumnWidth((short) 30);
		     sheet.addMergedRegion(new Region(0,(short)0,0,(short)(headers.length-1))); //合并第一行
		     sheet.addMergedRegion(new Region(1,(short)0,1,(short)(headers.length-1))); //合并第二行
			 HSSFCellStyle style=workbook.createCellStyle();
			 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		      style.setFillBackgroundColor(HSSFColor.AQUA.index);
		      style.setFillForegroundColor(HSSFColor.BLACK.index);
		      HSSFFont font = workbook.createFont();
		      font.setColor(HSSFColor.BLACK.index);
		      font.setFontHeightInPoints((short) 10);
		      font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		      style.setFont(font);
		         HSSFRow row =sheet.createRow(0);
		         HSSFCell cell0 = row.createCell(0);
		      cell0.setCellStyle(style);
		      HSSFRichTextString text0 =new HSSFRichTextString(title);
              cell0.setCellValue(text0);
              
              row= sheet.createRow(1);
              cell0 = row.createCell(0);
              cell0.setCellStyle(style);
              text0 = new HSSFRichTextString(subTitle);
              cell0.setCellValue(text0);
 		      
              row=sheet.createRow(2);
              for(short i = 0;i<headers.length;i++){
            	  HSSFCell cell = row.createCell(i);
            	  cell.setCellStyle(style);
            	  HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            	  cell.setCellValue(text);
              }
              Iterator<T>it =dataset.iterator();
              int index = 2;
              while(it.hasNext()){
            	  index++;
            	  row = sheet.createRow(index);
            	  T t = (T)it.next();
            	  //反射调用
            	  Field[] fields =t.getClass().getDeclaredFields();
            	  for(short i=0;i>fields.length;i++){
            		  HSSFCell cell =row.createCell(i);
            		  cell.setCellStyle(style);
            		 Field field =fields[i];
            		 String fieldName= field.getName();
            		 String getMethodName ="get"
            				 +fieldName.substring(0,1).toUpperCase()
            				 +fieldName.substring(1);
            		 Class tCls = t.getClass();
            		 Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
            		 Object value =getMethod.invoke(t,new Object[]{});
            		 String textValue;
            		 if(value!=null){
            			 textValue = value.toString()+"";
            		 }else{
            			 textValue="";
            		 }
            		 HSSFRichTextString richString = new HSSFRichTextString(textValue); 
            	     cell.setCellValue(richString);
            	  }
              }
                return workbook;
		} catch (Exception e) {
             e.printStackTrace();
		}
		   
		   return null;
		   
	   }
	   
	   
	   /**
	     * @Title: createExcelTemplate 
	     * @Description: 生成Excel导入模板  
	     * @param @param filePath  Excel文件路径
	     * @param @param handers   Excel列标题(数组)
	     * @param @param downData  下拉框数据(数组)
	     * @param @param downRows  下拉列的序号(数组,序号从0开始)
	     * @return void
	     * @throws
	     */
	    public  HSSFWorkbook createExcelTemplate(String filePath, String[] handers, 
	            List<String[]> downData, String[] downRows,String queryStr){
	        
	        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
	        
	        //表头样式
	        HSSFCellStyle style = wb.createCellStyle();  
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
	        
	        //字体样式
	        HSSFFont fontStyle = wb.createFont();    
	        fontStyle.setFontName("微软雅黑");    
	        fontStyle.setFontHeightInPoints((short)10);    
	        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	        style.setFont(fontStyle);
	        
	        //新建sheet
	        HSSFSheet sheet1 = wb.createSheet(filePath);
	        HSSFSheet sheet2 = wb.createSheet("Sheet2");
	        sheet1.addMergedRegion(new Region(0,(short)0,0,(short)(handers.length-1))); //合并第一行
	        sheet1.addMergedRegion(new Region(1,(short)0,1,(short)(handers.length-1))); //合并第二行
	        HSSFRow row0 =sheet1.createRow(0);
	         HSSFCell cell0 = row0.createCell(0);
	        HSSFRichTextString text0 =new HSSFRichTextString(filePath);
	        cell0.setCellStyle(style);
	        cell0.setCellValue(text0);
	        //生成空行
	        HSSFRow row1 =sheet1.createRow(1);
	        HSSFRichTextString text1 =new HSSFRichTextString(queryStr);
	        cell0 = row1.createCell(0);
            cell0.setCellStyle(style);
	        cell0.setCellValue(text1);
	        //生成sheet1内容
	        HSSFRow rowFirst = sheet1.createRow(2);//第一个sheet的第一行为标题
	        //写标题
	        for(int i=0;i<handers.length;i++){
	            HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
	            sheet1.setColumnWidth(i, 5000); //设置每列的列宽
	            cell.setCellStyle(style); //加样式
	            cell.setCellValue(handers[i]); //往单元格里写数据
	        }
	        
	        //设置下拉框数据
	        String[] arr = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
	        int index = 0;
	        HSSFRow row = null;
	        for(int r=0;r<downRows.length;r++){
	            String[] dlData = downData.get(r);//获取下拉对象
	            int rownum = Integer.parseInt(downRows[r]);
	            
	            if(dlData.length<5){ //255以内的下拉
	                //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
	                sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 50000, rownum ,rownum)); //超过255个报错 
	            } else { //255以上的下拉，即下拉列表元素很多的情况
	                
	                //1、设置有效性
	                   //String strFormula = "Sheet2!$A$1:$A$5000" ; //Sheet2第A1到A5000作为下拉列表来源数据
	                String strFormula = "Sheet2!$"+arr[index]+"$1:$"+arr[index]+"$5000"; //Sheet2第A1到A5000作为下拉列表来源数据
	                   sheet2.setColumnWidth(r, 4000); //设置每列的列宽
	                   //设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
	                      sheet1.addValidationData(SetDataValidation(strFormula, 1, 50000, rownum, rownum)); //下拉列表元素很多的情况
	                      
	                      //2、生成sheet2内容
	                      for(int j=0;j<dlData.length;j++){
	                    if(index==0){ //第1个下拉选项，直接创建行、列
	                        row = sheet2.createRow(j); //创建数据行
	                        sheet2.setColumnWidth(j, 4000); //设置每列的列宽
	                        row.createCell(0).setCellValue(dlData[j]); //设置对应单元格的值
	                        
	                    } else { //非第1个下拉选项
	                        
	                        int rowCount = sheet2.getLastRowNum();
	                        //System.out.println("========== LastRowNum =========" + rowCount);
	                        if(j<=rowCount){ //前面创建过的行，直接获取行，创建列
	                            //获取行，创建列
	                            sheet2.getRow(j).createCell(index).setCellValue(dlData[j]); //设置对应单元格的值
	                            
	                        } else { //未创建过的行，直接创建行、创建列
	                            sheet2.setColumnWidth(j, 4000); //设置每列的列宽
	                            //创建行、创建列
	                            sheet2.createRow(j).createCell(index).setCellValue(dlData[j]); //设置对应单元格的值
	                        }
	                    }
	                }
	                      index++;
	            }
	        }
	        return wb;
	    }
	   
	   
	   
	    /**
	     * 
	     * @Title: SetDataValidation 
	     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
	     * @param @param strFormula
	     * @param @param firstRow   起始行
	     * @param @param endRow     终止行
	     * @param @param firstCol   起始列
	     * @param @param endCol     终止列
	     * @param @return
	     * @return HSSFDataValidation
	     * @throws
	     */
	    private static HSSFDataValidation SetDataValidation(String strFormula, 
	            int firstRow, int endRow, int firstCol, int endCol) {
	        
	        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
	        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
	        DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
	        HSSFDataValidation dataValidation = new HSSFDataValidation(regions,constraint);
	        
	        dataValidation.createErrorBox("Error", "Error");
	        dataValidation.createPromptBox("", null);
	    
	        return dataValidation;
	    }
	    
	    
	    /**
	     * 
	     * @Title: setDataValidation 
	     * @Description: 下拉列表元素不多的情况(255以内的下拉)
	     * @param @param sheet
	     * @param @param textList
	     * @param @param firstRow
	     * @param @param endRow
	     * @param @param firstCol
	     * @param @param endCol
	     * @param @return
	     * @return DataValidation
	     * @throws
	     */
	    private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

	        DataValidationHelper helper = sheet.getDataValidationHelper();
	        //加载下拉列表内容
	        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
	        //DVConstraint constraint = new DVConstraint();
	        constraint.setExplicitListValues(textList);
	        
	        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
	        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
	    
	        //数据有效性对象
	        DataValidation data_validation = helper.createValidation(constraint, regions);
	        //DataValidation data_validation = new DataValidation(regions, constraint);
	    
	        return data_validation;
	    }
	    
	   
	   
	   
	   
	   
}
